#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "

insert into hive.edu_dws.wwq_dws_cust_day_cnt
with tmp as(
select
-- 判断字段
    id,--统计意向人数
    name,
    payment_state,--支付状态
    create_date_time,--创建日期
    deleted,--判断意向字段
    customer_id,--用户id
    appeal_status, --申诉状态 0 1(无效) 2
    creator,--销售id
    if(origin_type is not null ,origin_type,'未知') as origin_type,      -- 来源渠道
    if(origin_channel is not null ,origin_channel,'未知') as origin_channel,      -- 来源渠道
    if(itcast_school_id is not null ,itcast_school_id,0) as itcast_school_id,    -- 校区id
    if(itcast_school_name is not null ,itcast_school_name,'未知') as itcast_school_name,  -- 校区名称
    if(itcast_subject_name is not null ,itcast_subject_name,'未知') as itcast_subject_name, -- 学科名称
    if(itcast_subject_id is not null ,itcast_subject_id,0) as itcast_subject_id,   -- 学科id
    if(tdepart_id is not null ,tdepart_id,0) as tdepart_id,          -- 咨询中心id
    if(name is not null ,name,'未知') as tdepart_name,        -- 咨询中心名称
    substring(create_date_time,1,10) as c_day,   -- 天
    substring(create_date_time,1,7) as c_month,   -- 月
    substring(create_date_time,1,4) as c_year   -- 年
 from hive.edu_dwb.dwb_customer_relationship_wide
)
select
    c_day,
    origin_type,
    itcast_school_id,
    itcast_school_name,
    itcast_subject_id,
    itcast_subject_name,
    tdepart_id,
    tdepart_name,
    origin_channel,
    count(id) as r_num ,--意向人数
    count(if(payment_state='PAID',1,NULL)) as s_num ,--报名人数
    count(if(appeal_status <> 1,1,NULL)) as live_num,--有效线索人数
    IF(count(id) = 0, 0, count(if(payment_state='PAID',1,NULL)) / count(id)) as r_cnt,
    count(if(appeal_status <> 1 and origin_type = 'NETSERVICE' or origin_type= 'PRESIGNUP' ,1,NULL)) / count(if(origin_type ='NETSERVICE' or origin_type ='PRESIGNUP',id,0)) as online_cnt,
    case when grouping (itcast_school_id,itcast_school_name)=0 then'校区'
         when grouping (itcast_subject_id,itcast_subject_name)=0 then '学科'
         when grouping (itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name)=0 then '校区学科'
         when grouping (origin_channel)=0 then '渠道来源'
         when grouping (tdepart_id,tdepart_name)=0 then'咨询中心'
         when grouping (origin_type)=0 then '数据来源,线上线下'
         else '天'
    end as group_type
from tmp
group by
grouping sets (
    (c_day,itcast_school_id,itcast_school_name),  -- 每天各个校区报名人数
    (c_day,origin_type,itcast_school_id,itcast_school_name), -- 每天线上线下各校区报名人数
    (c_day,origin_type,itcast_subject_id,itcast_subject_name),    -- 每天线上线下各学科报名人数
    (c_day,origin_type,itcast_school_id,itcast_school_name),  -- 每天线上线下各校区各学科报名人数
    (c_day,origin_type,origin_channel),-- 每天各来源渠道报名人数
    (c_day,origin_type,tdepart_id,tdepart_name)  -- 每天线上线下各咨询中心报名人数
    );